package managers;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.ArrayList;

//import com.google.gson.Gson;

import business.Current;
import business.Module;
import business.Program;

public class MngPrograms extends Manager {
	
	public MngPrograms() {
		
	}

    public ArrayList<Module> getAllPrograms() {
        return getAllPrograms(0);
    }
    
	public ArrayList<Module> getAllPrograms(int profileID) {
		ArrayList<Module> modules = new ArrayList<Module>();
		
		try {
			String sql = "SELECT prg.id, prg.module AS moduleID, mdl.module, prg.program, prg.icon " +
						 "FROM programs prg " +
						 "INNER JOIN modules mdl ON prg.module = mdl.id " +
						 "INNER JOIN access acc ON prg.id = acc.program " +
						 "INNER JOIN profiles prf ON acc.profile = prf.id ";
			if (profileID == 0)	{
                    sql += "INNER JOIN users usr ON usr.profile = prf.id " +
                           "WHERE prg.modal = 0 AND usr.id = " + Current.user;
            }   else {
                    sql += "WHERE prg.modal = 0 AND prf.id = " + profileID;
            }
					 
            sql += " ORDER BY mdl.order, prg.order ";
			
			System.out.println("getAllPrograms: " + sql);
			rst = stmt.executeQuery(sql);
			
			HashMap<Byte, ArrayList<Program>> vhData = new HashMap<Byte, ArrayList<Program>>();
			
			byte mdlID = 0;
			while (rst.next()) {
				byte modID = rst.getByte("moduleID");
				
				if (mdlID != modID) {
					Module module = new Module();
					module.setId(modID);
					module.setModule(rst.getString("module"));
					
					modules.add(module);
					
					mdlID = modID;
				}
				
				Program program = new Program();
				program.setId(rst.getByte("id"));
				program.setProgram(rst.getString("program"));
                program.setIcon(rst.getString("icon"));
				
				ArrayList<Program> programs;
				if (vhData.containsKey(modID)) {
					programs = vhData.get(modID);
				}	else {
					programs = new ArrayList<Program>();
				}
				programs.add(program);
				vhData.put(modID, programs);
			}	
			
			for (Module module : modules) {
				module.setPrograms(vhData.get(module.getId()));
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		//Gson gson = new Gson();
		//System.out.println(gson.toJson(modules));
		
		return modules;
	}
	
	public ArrayList<Program> getProgramsByProfile(int prfID) {
		ArrayList<Program> programs = new ArrayList<Program>();
		
		try {
			String sql = "SELECT DISTINCT prg.id, prg.program, prg.icon " +
						  "FROM programs prg " +
						  "INNER JOIN access acc ON prg.id = acc.profile " +
						  "INNER JOIN profiles prf ON acc.profile = prf.id " +
						  "WHERE acc.profile = " + prfID + " AND prg.modal = 0";
			
			rst = stmt.executeQuery(sql);
				
			while (rst.next()) {
				Program prg = new Program();
				prg.setId(rst.getByte("id"));
				prg.setProgram(rst.getString("program"));
				prg.setIcon(rst.getString("icon"));
				
				programs.add(prg);
			}	
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return programs;
	}
	
	public ArrayList<Program> getPrograms(byte userID) {
		return getPrograms(userID, new Byte("0"));
	}
	
	public ArrayList<Program> getPrograms(byte userID, byte module) {
		ArrayList<Program> programs = new ArrayList<Program>();
		
		try {
			String sql = "SELECT DISTINCT prg.id, prg.program, prg.icon, prg.modal " +
						  "FROM programs prg " +
						  "INNER JOIN modules mdl ON prg.module = mdl.id " +
						  "INNER JOIN access acc ON prg.id = acc.program " + 
						  "INNER JOIN profiles prf ON acc.profile = prf.id " +
						  "INNER JOIN users usr ON prf.id = usr.profile " +
						  "WHERE usr.id = " + userID;
			if (module > 0)  sql +=	" AND mdl.id = " + module;
			sql += " ORDER BY prg.order";
			
			System.out.println("getPrograms: " + sql);
			
			rst = stmt.executeQuery(sql);
			
			String lst = "("; 
			
			while (rst.next()) {
				Program prg = new Program();
				prg.setId(rst.getByte("id"));
				prg.setProgram(rst.getString("program"));
				prg.setIcon(rst.getString("icon"));
				prg.setModal(rst.getByte("modal"));
				
				lst += prg.getId() + ", ";
				
				programs.add(prg);
			}	
			
			lst = lst.substring(0, lst.length() - 2) + ")";
			
			sql = "SELECT id, program, icon, modal " +
				  "FROM programs " +
				  "WHERE modal IN " + lst;
			
			rst = stmt.executeQuery(sql);
			
			while (rst.next()) {
				Program prg = new Program();
				prg.setId(rst.getByte("id"));
				prg.setProgram(rst.getString("program"));
				prg.setIcon(rst.getString("icon"));
				prg.setModal(rst.getByte("modal"));
				
				programs.add(prg);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return programs;
	}
	
	public Program getProgram(byte prgID) {
		Program program = null;
		String sql = "SELECT program, icon, modal FROM programs WHERE id = " + prgID;
		
		try {
			rst = stmt.executeQuery(sql);
			if (rst.next()) {
				program = new Program();
				program.setId(prgID);
				program.setProgram(rst.getString("program"));
				program.setIcon(rst.getString("icon"));
				program.setModal(rst.getByte("modal"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return program;
	}
}
